#include "server.h"
#include "ui_server.h"
#include <QTcpServer>
#include <QTcpSocket>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDateTime>
#include <QDir>

server::server(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::server)
{
    ui->setupUi(this);
    tcpServer = new QTcpServer();

    ui->listWidget->clear();
    ui->listWidget->insertItem(0,tr("当前无在线用户"));
    tcpServer->listen(QHostAddress::Any,8080);

    // init
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost"); // 服务器MySQL地址
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("Zp010521");
    db.setDatabaseName("LChat");//数据库名字

    // 判断是否建立了用户表
    if(!db.open())
    {
        qDebug() << "打开数据库失败" << endl;
    }

    // QDir dir;
    // QString dbPath = dir.absolutePath();
    // qDebug() << "Current working directory: " << dbPath;
    QSqlQuery sqlquery;
    if(!sqlquery.exec("CREATE TABLE if not exists user (id int auto_increment not null unique,name varchar(255) not null unique,password longtext not null,ip text,islogin int not null,PRIMARY KEY(id))CHARACTER SET utf8"))
    {
        qDebug() << "创建user表失败" << endl;
        qDebug() << sqlquery.lastError().text() << endl;
        return;
    }
    else
    {
        qDebug() << "创建user表成功" << endl;
    }

    // 下线所有用户
    sqlquery.exec("update user set islogin = 0");

    db.close();


    connect(tcpServer,&QTcpServer::newConnection,[=](){

        qDebug() << "有新的连接请求" << endl;

        QTcpSocket* newtcpSocket = tcpServer->nextPendingConnection();
        tcpSocket.push_back(newtcpSocket);
        connect(newtcpSocket, &QTcpSocket::disconnected, [&](){
            for(int i = 0; i < tcpSocket.size(); ++i)
            {
                if(tcpSocket.at(i) == newtcpSocket)
                {
                    tcpSocket.remove(i);
                    break;
                }
            }
        });

        QString ip = newtcpSocket->peerAddress().toString().section(":",3,3);
        int port = newtcpSocket->peerPort();

        QString str = QString("[%1:%2]").arg(ip).arg(port);
        qDebug() << str ;

        connect(newtcpSocket,&QTcpSocket::readyRead,[=](){
            // 读取缓冲区数据
            QByteArray buffer = newtcpSocket->readAll();

            qDebug() << "buffer = " << buffer << endl;

            if("login" == QString(buffer).section("##",0,0))
            {
                // login##id##pass##testport
                // 登陆
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                sqlquery.prepare("select * from user where name = :name");
                sqlquery.bindValue(":name",QString(buffer).section("##",1,1));
                sqlquery.exec();
                if(!sqlquery.next())
                {
                    // 未查找到该用户
                    newtcpSocket->write(QString("login error##no_user").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
                else
                {
                    // 用户存在
                    int id = sqlquery.value(0).toInt();
                    QString pwd = sqlquery.value(2).toString();
                    if(pwd == QString(buffer).section("##",2,2))
                    {
                        // 登录成功
                        newtcpSocket->write(QString("login successed##%1").arg(id).toUtf8());
                        sqlquery.prepare("update user set ip=:ip, islogin=1 where name = :name");
                        sqlquery.bindValue(":ip",ip);
                        sqlquery.bindValue(":name",QString(buffer).section("##",1,1));
                        sqlquery.exec();
                        newtcpSocket->flush();

                        QString uname = QString(buffer).section("##",1,1);
                        qDebug() << "login uname = " << uname << endl;
                        QTcpSocket* broadSock = new QTcpSocket;
                        int tport = QString(buffer).section("##", 3, 3).toInt();
                        qDebug() << "tport: " << tport;
                        broadSock->connectToHost(ip, tport);
                        broadcast[uname] = broadSock;


                        // 更新服务器界面
                        ui->listWidget->clear();
                        sqlquery.prepare("select * from user where islogin = 1");
                        sqlquery.exec();
                        if(sqlquery.next())
                        {
                            QString userid = sqlquery.value(0).toString();
                            QString username = sqlquery.value(1).toString();
                            QString userip = sqlquery.value(3).toString();
                            // qDebug()<<userid;
                            ui->listWidget->insertItem(0,"用户ID："+userid+"，用户昵称："+username+"，用户IP："+userip);
                            int rownum = 1;
                            while (sqlquery.next())
                            {
                                QString userid = sqlquery.value(0).toString();
                                QString username = sqlquery.value(1).toString();
                                QString userip = sqlquery.value(3).toString();
                                ui->listWidget->insertItem(rownum,"用户ID："+userid+"，用户昵称："+username+"，用户IP："+userip);
                                rownum++;
                            }
                        }
                        else
                        {
                            ui->listWidget->clear();
                            ui->listWidget->insertItem(0,tr("当前无在线用户"));
                        }
                    }
                    else
                    {
                        // 密码错误
                        newtcpSocket->write(QString("login error##errpwd").toUtf8());
                        newtcpSocket->flush();
                        db.close();
                    }
                }
            }
            else if("register" == QString(buffer).section("##",0,0))
            {
                // 注册环节
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                if(db.open())
                {
                    qDebug() << "成功打开数据库..." << endl;
                }
                else
                {
                    qDebug() << "数据库打开失败..." << endl;
                }
                QSqlQuery sqlquery;
                // 注册用户的时候需要进行判重
                sqlquery.prepare("select * from user where name = :name");
                sqlquery.bindValue(":name",QString(buffer).section("##",1,1));
                if(sqlquery.exec())
                {
                    qDebug() << "sql语句运行成功..." << endl;
                }
                else
                {
                    qDebug() << "sql语句运行失败" << endl;
                }
                if(!sqlquery.next())
                {
                    // 可以新建
                    sqlquery.clear();
                    sqlquery.prepare("insert into user values (null,:name,:password,null,0)");
                    sqlquery.bindValue(":name",QString(buffer).section("##",1,1));
                    sqlquery.bindValue(":password",QString(buffer).section("##",2,2));
                    sqlquery.exec();

                    sqlquery.clear();
                    sqlquery.prepare("select * from user where name = :name");
                    sqlquery.bindValue(":name",QString(buffer).section("##",1,1));
                    sqlquery.exec();// 获得新建的用户的id
                    sqlquery.next();
                    int newid = sqlquery.value(0).toInt();
                    sqlquery.exec("create table if not exists friend__" + QString::number(newid) +"(id int primary key, name varchar(255) not null unique,sendmessage int,sendfile int)CHARACTER SET utf8");


                    // 创建好友申请表
                    sqlquery.clear();
                    if(!sqlquery.exec("create table if not exists friendquest__" + QString::number(newid) +"(id int primary key, name varchar(255) not null unique)CHARACTER SET utf8"))
                    {
                        qDebug() << __LINE__ << "创建好友申请表失败" << endl;
                    }


                    newtcpSocket->write(QString("register successed").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
                else
                {// 有重名
                    newtcpSocket->write(QString("register error##same_name").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
            }
            else if("wantsendmessage" == QString(buffer).section("##",0,0))
            {
                // wantsendmessage##%mid##%fname
                // 想发信息，校验有没有这个人
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;

                // qDebug() << QString(buffer).section("##", 1, 1) << endl;
                sqlquery.prepare("select name from user where id = :id;");
                sqlquery.bindValue(":id", QString(buffer).section("##", 1, 1));
                sqlquery.exec();
                if(!sqlquery.next())
                {
                    qDebug() << __LINE__ << "异常！！！" << endl;
                }

                QString mname = sqlquery.value(0).toString();
                // qDebug() << mname << endl;

                sqlquery.clear();


                sqlquery.prepare("select * from user where name = :name");
                sqlquery.bindValue(":name",QString(buffer).section("##",2,2));
                sqlquery.exec();
                if(sqlquery.next())
                {
                    int otherid = sqlquery.value(0).toInt();
                    int thisid = QString(buffer).section("##",1,1).toInt();

                    // // 有这个人在，可以发消息
                    // /***
                    //  * 判断主方是否是客方好友，如果不是，那告诉客方加主方为临时好友。
                    //  *
                    // **/
                    // sqlquery.clear();
                    // QString sqlStr = "select id from friend__" + QString::number(otherid) + "where id = :tid;";
                    // sqlquery.exec();

                    QString resp = "";
                    // if(!sqlquery.next())
                    // {
                    // 不是客方好友。
                    // 判断客方是否在线（看是否有对应广播套接字即可），不在线服务端无法给他发去广播，就此作罢
                    // 原因是，既然主方也知道他不在线，给他发他也收不到消息，我们就认为此时主方不会给客方发去消息。
                    // 只等下次主方看到客方在线时，给客方发送消息，客方自会知道没有添加好友（当然这是理想情况）

                    // // 客方在线，则告知客方添加主方为临时好友，同时主方应向客方发出消息（申请加您为好友）
                    // QString fName = QString(buffer).section("##", 2, 2);
                    // if(broadcast.count(fName))
                    // {
                    //     QString reqs = "temporaryfriend##" + mname;
                    //     broadcast[fName]->write(reqs.toUtf8());
                    //     broadcast[fName]->flush();
                    // }

                    // resp = "presend_";
                    // }


                    // 发消息前把数据库准备好
                    if(thisid < otherid)
                    {
                        sqlquery.exec("CREATE TABLE if not exists chat__" + QString::number(thisid) + "__" + QString::number(otherid) + "(time datetime not null unique,id int,message longtext, PRIMARY KEY(time))CHARACTER SET utf8");
                    }
                    else
                    {
                        sqlquery.exec("CREATE TABLE if not exists chat__" + QString::number(otherid) + "__" + QString::number(thisid) + "(time datetime not null unique,id int,message longtext, PRIMARY KEY(time))CHARACTER SET utf8");
                    }

                    newtcpSocket->write(QString(resp + "wantsendmessage_ok##%1").arg(otherid).toUtf8());
                    newtcpSocket->flush();


                    db.close();
                }
                else
                {
                    // 查无此人，无法对话
                    newtcpSocket->write(QString("wantsendmessage_error").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
            }
            else if("temporarysendmessage" == QString(buffer).section("##",0,0))
            {
                // 临时对话  temporarysendmessage##主方id##客方name
                // 想发信息，校验有没有这个人
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;

                sqlquery.clear();
                sqlquery.prepare("select name from user where id = :mid");
                sqlquery.bindValue(":mid", QString(buffer).section("##", 1, 1));
                sqlquery.exec();

                QString mid = QString(buffer).section("##", 1, 1);
                QString fname = QString(buffer).section("##", 2, 2);
                QString mname;
                QString fid;
                if(sqlquery.next())
                {
                    mname = sqlquery.value(0).toString();
                }
                else
                {
                    qDebug() << "temporarysendmessage: 未搜索到主方，异常!!!" << endl;
                    db.close();
                    return;
                }

                sqlquery.clear();
                sqlquery.prepare("select * from user where name = :name");
                sqlquery.bindValue(":name",fname);
                sqlquery.exec();

                if(sqlquery.next())
                {
                    // 有这个人在
                    fid = sqlquery.value(0).toString();
                    qDebug() << __LINE__ << "mid: " << mid << ", mname: " << mname << "fid: " << fid << "fname: " << fname << endl;
                    // 是否在线？
                    int islogin = sqlquery.value(4).toInt();
                    if(islogin)
                    {
                        // 直接在双方friend表中加入彼此，然后通过广播套接字告知双方，在临时好友容器中加入彼此姓名

                        QString sqlString = QString("insert into friend__%1 values(%2, '%3', 0, 0)").arg(mid).arg(fid).arg(fname);

                        qDebug() << __LINE__ << ": " << sqlString << endl;
                        if(!sqlquery.exec(sqlString))
                        {
                            qDebug() << "__LINE__" << "异常!!!" << endl;
                            qDebug() << "__LINE__: " << __LINE__ << sqlquery.lastError().text() << endl;
                        }
                        else
                        {
                            qDebug() << __LINE__ << "临时对话添加成功，主方：" << mname;
                        }

                        sqlString = QString("insert into friend__%1 values(%2, '%3', 0, 0)").arg(fid).arg(mid).arg(mname);
                        qDebug() << __LINE__ << ": " << sqlString << endl;

                        if(!sqlquery.exec(sqlString))
                        {
                            qDebug() << "__LINE__" << "异常!!!" << endl;
                            qDebug() << "__LINE__: " << __LINE__ << sqlquery.lastError().text() << endl;
                        }
                        else
                        {
                            qDebug() << __LINE__ << "临时对话添加成功，客方：" << fname;
                        }

                        broadcast[mname]->write(QString("temporaryfriend##" + fname).toUtf8());
                        broadcast[mname]->flush();
                        broadcast[fname]->write(QString("temporaryfriend##" + mname).toUtf8());
                        broadcast[fname]->flush();


                        // 发消息前把数据库准备好
                        int thisid = mid.toInt();
                        int otherid = fid.toInt();
                        if(thisid < otherid)
                        {
                            sqlquery.exec("CREATE TABLE if not exists chat__" + QString::number(thisid) + "__" + QString::number(otherid) + "(time datetime not null unique,id int,message longtext, PRIMARY KEY(time))CHARACTER SET utf8");
                        }
                        else
                        {
                            sqlquery.exec("CREATE TABLE if not exists chat__" + QString::number(otherid) + "__" + QString::number(thisid) + "(time datetime not null unique,id int,message longtext, PRIMARY KEY(time))CHARACTER SET utf8");
                        }

                        newtcpSocket->write(QString("temporarysendmessage_ok##" + fid).toUtf8());
                        newtcpSocket->flush();
                        db.close();
                    }
                    else
                    {
                        qDebug() << "no login" << endl;
                        newtcpSocket->write(QString("temporarysendmessage_offline").toUtf8());
                        newtcpSocket->flush();
                        db.close();
                    }
                }
                else
                {
                    // 查无此人，无法对话
                    newtcpSocket->write(QString("temporarysendmessage_error").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
            }
            else if("chat_history" == QString(buffer).section("##",0,0))
            {
                // idone是发信息的，idtwo是收信息的，所以one已经阅览了two的所有消息
                int idone = QString(buffer).section("##",1,1).toInt();
                int idtwo = QString(buffer).section("##",2,2).toInt();
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                QString sqlstring = "";

                int littid = idone;
                int bigid = idtwo;
                if(littid > bigid)
                {
                    int t = littid;
                    littid = bigid;
                    bigid = t;
                }

                // 总消息数
                int totalCnt = 0;
                sqlstring = "select count(*) from chat__" + QString::number(littid) + "__" + QString::number(bigid);
                sqlquery.exec(sqlstring);
                if(sqlquery.next())
                {
                    totalCnt = sqlquery.value(0).toInt();
                }
                else
                {
                    qDebug() << __LINE__ << ": sql语句有误：" << sqlstring << endl;
                }

                sqlstring.clear();
                sqlstring = "select * from chat__" + QString::number(littid) + "__" + QString::number(bigid) + " order by time desc limit 20";

                qDebug()<<sqlstring;
                sqlquery.exec(sqlstring);

                if(sqlquery.next())
                {
                    QString history = "##" + sqlquery.value(0).toDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz")+ "##" + sqlquery.value(1).toString()+ "##" + sqlquery.value(2).toString();
                    int hisnum = 1;
                    while(sqlquery.next())
                    {
                        hisnum++;
                        QDateTime time = sqlquery.value(0).toDateTime();
                        QString timestr = time.toString("yyyy-MM-dd hh:mm:ss.zzz");
                        history = history + "##" + timestr;// 时间
                        history = history + "##" + sqlquery.value(1).toString();// 谁发的
                        history = history + "##" + sqlquery.value(2).toString();// 内容
                    }
                    history = "chat_history_ok##" + QString::number(totalCnt) + "##" + QString::number(hisnum) + history;
                    newtcpSocket->write(history.toUtf8());
                    newtcpSocket->flush();

                    QString sqlstring = "update friend__" + QString::number(idone) + " set sendmessage = 0 where id = :id";
                    db.setHostName("localhost"); // 服务器MySQL地址
                    db.setPort(3306);
                    db.setUserName("root");
                    db.setPassword("Zp010521");
                    db.setDatabaseName("LChat");//数据库名字
                    db.open();
                    QSqlQuery sqlquery;
                    sqlquery.prepare(sqlstring);
                    sqlquery.bindValue(":id", idtwo);
                    sqlquery.exec();
                    db.close();
                }
                else
                {
                    // 无历史记录
                    newtcpSocket->write(QString("chat_history_error").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
            }
            else if("chat_send" == QString(buffer).section("##",0,0))
            {
                // one发消息的，two收消息的
                QDateTime nowstr = QDateTime::fromString(QString(buffer).section("##",1,1), "yyyy-MM-dd hh:mm:ss.zzz");
                int idone = QString(buffer).section("##",2,2).toInt();
                int idtwo = QString(buffer).section("##",3,3).toInt();
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                QString sqlstring = "";
                if(idone < idtwo)
                {
                    sqlstring = "insert into chat__" + QString::number(idone) + "__" + QString::number(idtwo) + " values(:time,:id,:message)";
                }
                else
                {
                    sqlstring = "insert into chat__" + QString::number(idtwo) + "__" + QString::number(idone) + " values(:time,:id,:message)";
                }
                qDebug()<<sqlstring;
                sqlquery.prepare(sqlstring);
                sqlquery.bindValue(":time",nowstr);
                sqlquery.bindValue(":id",idone);
                sqlquery.bindValue(":message",QString(buffer).section("##",4,4));
                sqlquery.exec();

                sqlstring = "update friend__" + QString::number(idtwo) + " set sendmessage = 1 where id = :id";
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                sqlquery.clear();
                sqlquery.prepare(sqlstring);
                sqlquery.bindValue(":id", idone);
                sqlquery.exec();
                db.close();
            }
            else if("logout" == QString(buffer).section("##",0,0))
            {
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                sqlquery.prepare("update user set islogin=0 where id = :id");
                sqlquery.bindValue(":id",QString(buffer).section("##",1,1));
                sqlquery.exec();

                // 拿到用户的名称
                sqlquery.clear();
                sqlquery.prepare("select name from user where id = :id");
                sqlquery.bindValue(":id",QString(buffer).section("##",1,1));
                sqlquery.exec();
                sqlquery.next();
                QString outname = sqlquery.value(0).toString();
                // 删去和这个用户的广播套接字
                auto it = broadcast.find(outname);
                broadcast[outname]->deleteLater();
                broadcast.erase(it);

                // 更新服务器界面
                ui->listWidget->clear();
                sqlquery.prepare("select * from user where islogin = 1");
                sqlquery.exec();
                if(sqlquery.next())
                {
                    QString userid = sqlquery.value(0).toString();
                    QString username = sqlquery.value(1).toString();
                    QString userip = sqlquery.value(3).toString();
                    // qDebug()<<userid;
                    ui->listWidget->insertItem(0,"用户ID："+userid+",用户昵称:"+username+",用户IP:"+userip);
                    int rownum = 1;
                    while (sqlquery.next())
                    {
                        QString userid = sqlquery.value(0).toString();
                        QString username = sqlquery.value(1).toString();
                        QString userip = sqlquery.value(3).toString();
                        ui->listWidget->insertItem(rownum,"用户ID："+userid+",用户昵称:"+username+",用户IP:"+userip);
                        rownum++;
                    }
                }
                else
                {
                    ui->listWidget->clear();
                    ui->listWidget->insertItem(0,tr("当前无在线用户"));
                }
            }
            else if("getfriendlist" == QString(buffer).section("##",0,0))
            {
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                sqlquery.exec("select * from friend__" + QString(buffer).section("##",1,1));

                // 每次导入单个好友的各个信息（不同容器相同索引位描述同一个好友）
                if(sqlquery.next())
                {
                    QList <QString> friendlist; // name
                    QList <QString> friendsendfilelist; // 是否有消息
                    QList <QString> friendsendmessagelist; // 是否有发送文件
                    friendlist.append(sqlquery.value(1).toString());

                    int sendmessagenum = 0; // 统计所有好友未读消息数量总和
                    if(sqlquery.value(2).toString() == "1")
                    {
                        sendmessagenum++;
                    }
                    friendsendmessagelist.append(sqlquery.value(2).toString());
                    int sendfilenum = 0; // 统计所有发送文件的好友总数
                    if(sqlquery.value(3).toString() == "1")
                    {
                        sendfilenum++;
                    }
                    friendsendfilelist.append(sqlquery.value(3).toString());

                    QString friends = "";

                    while(sqlquery.next())
                    {
                        // 下一个好友
                        friendlist.append(sqlquery.value(1).toString());
                        if(sqlquery.value(2).toString() == "1")
                        {
                            sendmessagenum++;
                        }
                        friendsendmessagelist.append(sqlquery.value(2).toString());
                        if(sqlquery.value(3).toString() == "1")
                        {
                            sendfilenum++;
                        }
                        friendsendfilelist.append(sqlquery.value(3).toString());
                    }

                    int onlinefriendnum = 0;
                    for( int i = 0; i < friendlist.length(); i++)
                    {
                        sqlquery.prepare("select * from user where name = :name");
                        sqlquery.bindValue(":name",friendlist.at(i));
                        sqlquery.exec();
                        sqlquery.next();
                        QString userip = sqlquery.value(3).toString();
                        if(sqlquery.value(4).toInt() == 1)
                        {
                            onlinefriendnum++;
                            friends = "##" + friendlist.at(i) + "##1##" + userip +"##"+friendsendmessagelist.at(i)+"##"+ friendsendfilelist.at(i)+ friends;
                        }
                        else
                        {
                            friends = "##" + friendlist.at(i) + "##0##" + userip +"##"+friendsendmessagelist.at(i)+"##"+ friendsendfilelist.at(i)+ friends;
                        }
                    }
                    friends = "getfriendlist_ok##" + QString::number(friendlist.length()) +"##"+QString::number(onlinefriendnum)+"##"+QString::number(sendmessagenum)+"##"+QString::number(sendfilenum)+ friends;
                    qDebug()<<friends;

                    // getfriendlist_ok##好友数##在线好友数##所有好友未读消息数量总和##所有发送文件的好友总数##好友1name##好友1状态##好友1ip##好友1是否有消息##好友1是否发送文件##
                    newtcpSocket->write(friends.toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
                else
                {
                    qDebug() << "没有朋友" << endl;
                    // 无朋友
                    newtcpSocket->write(QString("getfriendlist_error").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
            }
            else if("add_friend" == QString(buffer).section("##",0,0))
            {
                // add_friend##uid##uname##fname
                int whowantadd_id = QString(buffer).section("##",1,1).toInt();
                QString whoname = QString(buffer).section("##", 2, 2);
                QString friend_name = QString(buffer).section("##",3 ,3);

                qDebug() << whowantadd_id << friend_name;

                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                sqlquery.prepare("select * from user where name = :name");
                sqlquery.bindValue(":name",friend_name);
                sqlquery.exec();

                qDebug() << "friend_name = " << friend_name << endl;
                if(sqlquery.next())
                {
                    int friend_id = sqlquery.value(0).toInt();
                    qDebug() <<friend_id;
                    sqlquery.clear();
                    QString sqlstring = "insert into friendquest__" + QString::number(friend_id) + " values(:id,:name)";
                    qDebug()<<sqlstring;
                    sqlquery.prepare(sqlstring);
                    sqlquery.bindValue(":id",whowantadd_id);
                    sqlquery.bindValue(":name",whoname);
                    sqlquery.exec();
                    qDebug() << "__LINE__: " << __LINE__ << sqlquery.lastError().text() << endl;
                    newtcpSocket->write(QString("add_friend_ok").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
                else
                {
                    // 没这人
                    qDebug() <<"e";
                    newtcpSocket->write(QString("add_friend_error").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
            }
            else if("dealwithfriendquest" == QString(buffer).section("##",0,0))
            {
                qDebug() << "处理好友申请：" << QString(buffer) << endl;
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();

                // dealwithfriendquest##yes或no##我方id##我方name##客方name

                QString myid = QString(buffer).section("##",2, 2);
                QString myname = QString(buffer).section("##", 3, 3);
                QString fname = QString(buffer).section("##", 4, 4);
                qDebug() << "myid = " << myid << ", mname = " << myname << ", fname = " << fname << endl;

                // 无论是拒绝还是同意都是对申请的处理，将好友申请从表中删去
                QSqlQuery sqlquery;
                QString sqlString = QString("delete from friendquest__%1 where name = '%2';").arg(myid).arg(fname);
                if(!sqlquery.exec(sqlString))
                {
                    qDebug() << __LINE__ << "sql语句有误: " << sqlString << endl;
                    qDebug() <<__LINE__<< myid << myname << fname;
                }
                else
                {
                    qDebug() << "成功删除本条申请记录: " << fname << "对我的申请记录" << endl;
                }

                if("no" == QString(buffer).section("##", 1, 1))
                {
                    db.close();
                }

                else
                {
                    // 同意添加
                    QSqlQuery sqlquery;
                    QString sqlString = R"(select * from user where name = ')" + fname + R"(')";
                    if(sqlquery.exec(sqlString))
                    {
                        qDebug() << sqlquery.lastError().text() << endl;
                    }
                    if(!sqlquery.next())
                    {
                        qDebug() << "理论上好友必存在，请检查sql语句: " << sqlString << endl;
                    }

                    QString fid = sqlquery.value(0).toString();
                    sqlString = QString("insert into friend__%1 values(%2, '%3', 0, 0)").arg(myid).arg(fid).arg(fname);
                    if(!sqlquery.exec(sqlString))
                    {
                        qDebug() << __LINE__ << "同意添加好友，插入有误: " << sqlquery.lastError().text() << endl;
                    }
                    else
                    {
                        qDebug() << __LINE__ << "同意添加好友: 执行成功" << endl;
                    }

                    sqlString = QString("insert into friend__%1 values(%2, '%3', 0, 0)").arg(fid).arg(myid).arg(myname);
                    if(!sqlquery.exec(sqlString))
                    {
                        qDebug() << "同意添加好友，插入有误: " << sqlquery.lastError().text() << endl;
                    }
                    else
                    {
                        qDebug() << __LINE__ << "同意添加好友: 执行成功" << endl;
                    }

                    auto it = broadcast.find(fname);
                    if(it != broadcast.end())
                    {
                        QString resp = "add_friend_ok##" + myname;
                        broadcast[fname]->write(resp.toUtf8());
                        broadcast[fname]->flush();
                    }
                    db.close();
                }
            }
            else if("delete_friend" == QString(buffer).section("##",0,0))
            {
                // delete_friend##我方id##对方name
                QString mid = QString(buffer).section("##",1,1);
                QString fname = QString(buffer).section("##",2,2);

                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                QString sqlString = QString("delete from friend__%1 where name = '%2'").arg(mid).arg(fname);
                if(sqlquery.exec(sqlString))
                {
                    newtcpSocket->write(QString("delete_friend_ok").toUtf8());
                    newtcpSocket->flush();
                }

                // （双向删除）从对方好友列表中删去
                /***
                 * 1. 根据fname查询fid
                 * 2. 在friend__fid中删去mid对应的记录
                */

                sqlString = QString("select * from user where name = '%1'").arg(fname);
                sqlquery.exec(sqlString);
                if(sqlquery.next())
                {
                    QString fid = sqlquery.value(0).toString();
                    sqlString = QString("delete from friend__%1 where id = %2").arg(fid).arg(mid);
                    if(!sqlquery.exec(sqlString))
                    {
                        qDebug() << "请检查sql语句：" << sqlString << endl;
                    }
                    else
                    {
                        qDebug() << "双向删除: " << sqlString << endl;
                    }
                }
                else
                {
                    qDebug() << "sql语句有误: " << sqlString << endl;
                }
            }

            else if("want_send_file" == QString(buffer).section("##",0,0))
            {
                // 1给2发,1是id,2是name;在2的表中显示1的信息
                int userid = QString(buffer).section("##",1,1).toInt();
                qDebug()<<buffer;
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                sqlquery.prepare("select * from user where name = :name");
                sqlquery.bindValue(":name",QString(buffer).section("##",2,2));
                sqlquery.exec();
                sqlquery.next();
                int otheruserid = sqlquery.value(0).toInt();

                QString sqlstring = "update friend__" + QString::number(otheruserid) + " set sendfile = 1 where id = :id";
                sqlquery.prepare(sqlstring);
                sqlquery.bindValue(":id",userid);
                sqlquery.exec();
            }
            else if("send_file_ok" == QString(buffer).section("##",0,0))
            {// 1给2发,1是id,2是name;在2的表中显示1的信息
                int userid = QString(buffer).section("##",1,1).toInt();
                qDebug()<<buffer;
                QString sqlstring = "update friend__" + QString::number(userid) + " set sendfile = 0 where name = :name";
                qDebug()<<sqlstring;
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                sqlquery.prepare(sqlstring);
                sqlquery.bindValue(":name", QString(buffer).section("##",2,2));
                sqlquery.exec();
            }
            else if("send_file_miss" == QString(buffer).section("##",0,0))
            {
                int userid = QString(buffer).section("##",1,1).toInt();
                qDebug()<< "LINE = " << __LINE__ << ": " << buffer;
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                sqlquery.prepare("select * from user where name = :name");
                sqlquery.bindValue(":name",QString(buffer).section("##",2,2));
                sqlquery.exec();
                sqlquery.next();
                int otheruserid = sqlquery.value(0).toInt();

                QString sqlstring = "update friend__" + QString::number(otheruserid) + " set sendfile = 0 where id = :id";
                sqlquery.prepare(sqlstring);
                sqlquery.bindValue(":id",userid);
                sqlquery.exec();
            }
            else if("getfriendquest" == QString(buffer).section("##", 0, 0))
            {
                qDebug() << "getfriendquest...." << endl;
                qDebug() << "LINE = " << __LINE__ << ", buffer = " << buffer;
                // getfriendquest##id
                // 获取好友申请表(friendquest__)
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();

                QString mid = QString(buffer).section("##", 1, 1);
                QSqlQuery sqlquery;
                QString sqlString = "select * from friendquest__" + mid;
                sqlquery.exec(sqlString);

                qDebug() << "LINE = " << __LINE__ << ", mid = " << mid << endl;
                QString friendqs;
                int cnt = 0;
                if(sqlquery.next())
                {
                    // 有好友申请
                    qDebug() << "mid：" << "有好友申请";
                    friendqs += "##" + sqlquery.value(1).toString();
                    ++cnt;

                    while(sqlquery.next())
                    {
                        friendqs += "##" + sqlquery.value(1).toString();
                        ++cnt;
                    }


                    // getfriendquest_ok##申请数##name1##name2##name3
                    QString resp = "getfriendquest_ok##" + QString::number(cnt) + friendqs;
                    qDebug() << "getfriendquest_ok：" << resp;
                    newtcpSocket->write(resp.toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }
                else
                {
                    qDebug() << "getfriendquest_error" << endl;
                    // 没有好友申请记录
                    newtcpSocket->write("getfriendquest_error");
                    newtcpSocket->flush();
                    db.close();
                }

            }

            else if("checkmessage" == QString(buffer).section("##", 0, 0))
            {
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;

                // checkmessage##mid##fid##oldtotalCnt
                QString mid, fid;
                QString littid = mid = QString(buffer).section("##", 1, 1);
                QString bigid = fid = QString(buffer).section("##", 2, 2);
                int oldtotalCnt = QString(buffer).section("##", 3, 3).toInt();
                QString sqlstring;

                if(littid > bigid)
                    littid.swap(bigid);

                qDebug() << "oldtotalCnt = " << oldtotalCnt << endl;

                sqlstring = QString("select count(*) from chat__%1__%2;").arg(littid).arg(bigid);
                sqlquery.exec(sqlstring);
                if(sqlquery.next())
                {
                    int curtotalCnt = sqlquery.value(0).toInt();

                    QString resp;
                    if(oldtotalCnt != curtotalCnt)
                    {
                        int newCnt = curtotalCnt - oldtotalCnt; // 新的消息条数
                        qDebug() << "curtotalCnt = " << curtotalCnt << endl;
                        qDebug() << "newCnt = " << newCnt << endl;
                        // 有新的对话内容
                        sqlstring = QString("select * from chat__%1__%2 order by time desc limit %3").arg(littid).arg(bigid).arg(newCnt);
                        sqlquery.exec(sqlstring);

                        if(sqlquery.next())
                        {
                            QString resp = "##" + sqlquery.value(0).toDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz")+ "##" + sqlquery.value(1).toString()+ "##" + sqlquery.value(2).toString();
                            while(sqlquery.next())
                            {
                                QDateTime time = sqlquery.value(0).toDateTime();
                                QString timestr = time.toString("yyyy-MM-dd hh:mm:ss.zzz");
                                resp = resp + "##" + timestr;// 时间
                                resp = resp + "##" + sqlquery.value(1).toString();// 谁发的
                                resp = resp + "##" + sqlquery.value(2).toString();// 内容
                            }
                            resp = "hasnewmessage##" + QString::number(curtotalCnt) + "##" + QString::number(newCnt) + resp;
                            newtcpSocket->write(resp.toUtf8());
                            newtcpSocket->flush();

                            sqlstring = "update friend__" + mid + " set sendmessage = 0 where id = :id";
                            sqlquery.clear();
                            sqlquery.prepare(sqlstring);
                            sqlquery.bindValue(":id", fid);
                            sqlquery.exec();
                            db.close();
                        }
                        else
                        {
                            qDebug() << __LINE__ << "：sql语句有误：" << sqlstring << endl;
                        }
                    }
                    else
                    {
                        resp = "nothasnewmessage";
                        tcpSocket[0]->write(resp.toUtf8());
                        tcpSocket[0]->flush();
                        db.close();
                    }
                }
                else
                {
                    qDebug() << __LINE__ << "：sql语句有误：" << sqlstring << endl;
                }
            }
            else if("getallchathistory" == QString(buffer).section("##", 0, 0))
            {
                // getallchathistory##mid##fid

                int mid = QString(buffer).section("##",1,1).toInt();
                int fid = QString(buffer).section("##",2,2).toInt();
                db.setHostName("localhost"); // 服务器MySQL地址
                db.setPort(3306);
                db.setUserName("root");
                db.setPassword("Zp010521");
                db.setDatabaseName("LChat");//数据库名字
                db.open();
                QSqlQuery sqlquery;
                QString sqlstring = "";

                int littid = mid;
                int bigid = fid;

                if(littid > bigid)
                {
                    int t = littid;
                    littid = bigid;
                    bigid = t;
                }


                sqlstring.clear();
                sqlstring = "select * from chat__" + QString::number(littid) + "__" + QString::number(bigid) + " order by time desc";

                qDebug() << sqlstring;
                sqlquery.exec(sqlstring);

                if(sqlquery.next())
                {
                    QString history = "##" + sqlquery.value(0).toDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz")+ "##" + sqlquery.value(1).toString()+ "##" + sqlquery.value(2).toString();
                    int hisnum = 1;
                    while(sqlquery.next())
                    {
                        hisnum++;
                        QDateTime time = sqlquery.value(0).toDateTime();
                        QString timestr = time.toString("yyyy-MM-dd hh:mm:ss.zzz");
                        history = history + "##" + timestr;// 时间
                        history = history + "##" + sqlquery.value(1).toString();// 谁发的
                        history = history + "##" + sqlquery.value(2).toString();// 内容
                    }
                    history = "getallchathistory_ok##" + QString::number(hisnum) + "##" + QString::number(hisnum) + history;
                    newtcpSocket->write(history.toUtf8());
                    newtcpSocket->flush();

                    QString sqlstring = "update friend__" + QString::number(mid) + " set sendmessage = 0 where id = :id";
                    db.setHostName("localhost"); // 服务器MySQL地址
                    db.setPort(3306);
                    db.setUserName("root");
                    db.setPassword("Zp010521");
                    db.setDatabaseName("LChat");//数据库名字
                    db.open();
                    QSqlQuery sqlquery;
                    sqlquery.prepare(sqlstring);
                    sqlquery.bindValue(":id", fid);
                    sqlquery.exec();
                    db.close();
                }
                else
                {
                    // 无历史记录
                    newtcpSocket->write(QString("getallchathistory_error").toUtf8());
                    newtcpSocket->flush();
                    db.close();
                }

            }
        });
    });
}

server::~server()
{
    tcpServer->close();
    tcpServer->deleteLater();
    delete ui;
}

void server::closeEvent(QCloseEvent *e)
{
    db.setHostName("localhost"); // 服务器MySQL地址
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("Zp010521");
    db.setDatabaseName("LChat");//数据库名字
    db.open();

    QString sqlstring = "update";


    db.close();
}
